import os
from datetime import datetime

import pandas as pd
from zc_core.client.mongo_client import Mongo
from scrapy.utils.project import get_project_settings

from zc_core.util.batch_gen import time_to_batch_no

meta_map = [
    {'col': '_id', 'title': '商品编号'},
    {'col': 'skuName', 'title': '商品名称'},
    {'col': 'salePrice', 'title': '销售价'},
    {'col': 'catalog1Name', 'title': '一级分类'},
    {'col': 'supplierId', 'title': '供应商编号'},
    {'col': 'supplierName', 'title': '供应商名称'},
    {'col': 'supplierSkuId', 'title': '供应商商品编号'},
    {'col': 'stock', 'title': '库存'},
]


def export_data(table, file, query={}):
    # 结构组装
    columns = list()
    headers = list()
    for meta in meta_map:
        columns.append(meta.get('col'))
        headers.append(meta.get('title'))

    # 数据查询
    rows = Mongo().list(
        collection=table,
        fields=columns,
        query=query,
        sort=[]
    )
    # print(rows)
    # 数据转换
    # for row in rows:
    #     row['publishTime'] = format_time(row.pop('publishTime'))
    #     row['deadlineTime'] = format_time(row.pop('deadlineTime'))
    #     row['validityDate'] = format_time(row.pop('validityDate'))

    # 写入Excel
    if rows:
        write = pd.ExcelWriter(file)
        df = pd.DataFrame(rows)
        df.to_excel(write, sheet_name='数据', columns=columns, header=headers, index=False)
        write.save()
        print('导出成功~')
    else:
        print('无数据~')


if __name__ == '__main__':
    bot_name = get_project_settings().get('BOT_NAME')
    # batch_no = '20210831'
    batch_no = time_to_batch_no(datetime.now())
    export_data(
        table='data_{}'.format(batch_no),
        file=f'/work/甬工惠_{batch_no}.xlsx',
        query={
        },
    )